from flask import Blueprint, jsonify, current_app, request
from models import Student, db, Clazz  # 假设 Student 是学生模型，db 是数据库实例
from sqlalchemy.exc import IntegrityError
from sqlalchemy import asc

api_bp = Blueprint('student', __name__)


# 1. 获取学生列表
@api_bp.route('/students', methods=['GET'])
def get_students():
    try:
        # 分页参数处理
        page = request.args.get('page', 1, type=int)
        page_size = request.args.get('page_size', 10, type=int)
        page_size = min(max(page_size, 1), 100)  # 限制每页最大数量

        # 关键词查询参数
        keyword = request.args.get('kw', '').strip()

        # 构建基础查询
        query = Student.query.join(
            Clazz, Student.class_id == Clazz.id, isouter=True
        ).add_columns(
            Student.id,
            Student.name,
            Student.studentId,
            Student.gender,
            Student.grade,
            Student.class_id,
            Student.major,
            Student.phone,
            Student.gpa,
            Clazz.name.label('class_name')
        ).order_by(asc(Student.id))

        # 添加关键词查询条件
        if keyword:
            keyword = f'%{keyword}%'  # 模糊查询
            query = query.filter(
                (Student.name.ilike(keyword)) |
                (Student.studentId.ilike(keyword)) |
                (Student.major.ilike(keyword)) |
                (Clazz.name.ilike(keyword))  # 支持班级名称搜索
            )

        # 查询总记录数（带关键词条件）
        total_items = query.count()

        # 计算总页数
        total_pages = (total_items + page_size - 1) // page_size
        page = max(1, min(page, total_pages))  # 防止页码越界

        # 执行分页查询
        students = query.offset((page - 1) * page_size).limit(page_size).all()

        # 组装返回数据
        data = []
        for student in students:
            data.append({
                "id": student.id,
                "name": student.name,
                "student_id": student.studentId,
                "gender": student.gender,
                "grade": student.grade,
                "class_id": student.class_id,
                "class_name": student.class_name,
                "major": student.major,
                "phone": student.phone,
                "gpa": float(student.gpa)
            })

        return jsonify({
            "message": "获取学生列表成功",
            "data": data,
            "pagination": {
                "current_page": page,
                "page_size": page_size,
                "total_pages": total_pages,
                "total_items": total_items
            }
        }), 200

    except Exception as e:
        current_app.logger.error(f"获取学生列表失败: {str(e)}", exc_info=True)
        return jsonify({"message": "获取学生列表失败"}), 500


# 2. 添加学生
@api_bp.route('/students', methods=['POST'])
def add_student():
    try:
        # 校验请求参数
        data = request.json
        required_fields = ["name", "student_id", "gender", "grade", "class_id", "major", "phone", "gpa"]
        for field in required_fields:
            if field not in data:
                return jsonify({"message": f"缺少必要参数: {field}"}), 400

        # 检查学生ID是否已存在
        existing_student = Student.query.filter_by(studentId=data["student_id"]).first()
        if existing_student:
            return jsonify({"message": f"学生ID {data['student_id']} 已存在"}), 400

        new_student = Student(
            name=data["name"],
            studentId=data["student_id"],
            gender=data["gender"],
            grade=data["grade"],
            class_id=data["class_id"],
            major=data["major"],
            phone=data["phone"],
            gpa=data["gpa"]
        )
        db.session.add(new_student)
        db.session.commit()
        return jsonify({
            "message": "学生添加成功",
            "student_id": new_student.id
        }), 201

    except IntegrityError:
        db.session.rollback()
        current_app.logger.error("数据库操作冲突")
        return jsonify({"message": "添加学生失败"}), 500
    except Exception as e:
        db.session.rollback()
        current_app.logger.error(f"添加学生失败: {str(e)}", exc_info=True)
        return jsonify({"message": "添加学生失败"}), 500


# 3. 更新学生信息
@api_bp.route('/students/<int:student_id>', methods=['PUT'])
def update_student(student_id):
    try:
        # 校验请求参数
        data = request.json

        # 查询学生记录
        student = Student.query.get(student_id)

        if not student:
            return jsonify({"message": "学生记录不存在"}), 404

        # 检查学生ID是否被修改且新ID已存在
        if "student_id" in data and data["student_id"] != student.studentId:
            existing_student = Student.query.filter(
                Student.studentId == data["student_id"],
                Student.id != student_id
            ).first()
            if existing_student:
                return jsonify({"message": f"学生ID {data['student_id']} 已被其他学生使用"}), 400

        # 更新学生信息
        if "name" in data:
            student.name = data["name"]
        if "student_id" in data:
            student.studentId = data["student_id"]
        if "gender" in data:
            student.gender = data["gender"]
        if "grade" in data:
            student.grade = data["grade"]
        if "class_id" in data:
            student.class_id = data["class_id"]
        if "major" in data:
            student.major = data["major"]
        if "phone" in data:
            student.phone = data["phone"]
        if "gpa" in data:
            student.gpa = data["gpa"]

        db.session.commit()

        return jsonify({
            "message": "学生信息更新成功",
            "student_id": student.id
        }), 200

    except Exception as e:
        db.session.rollback()
        current_app.logger.error(f"更新学生信息失败: {str(e)}", exc_info=True)
        return jsonify({"message": "更新学生信息失败"}), 500

# 4. 删除学生
@api_bp.route('/students/<int:student_id>', methods=['DELETE'])
def delete_student(student_id):
    try:
        # 查询并删除学生记录
        student = Student.query.get(student_id)

        if not student:
            return jsonify({"message": "学生记录不存在"}), 404

        db.session.delete(student)
        db.session.commit()

        return jsonify({"message": "学生已删除"}), 200

    except Exception as e:
        db.session.rollback()
        current_app.logger.error(f"删除学生失败: {str(e)}", exc_info=True)
        return jsonify({"message": "删除学生失败"}), 500
